package com.main;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.entity.Lock;
import com.entity.Person;
import com.entity.Province;
import com.entity.User;
import com.entity.UserTable;
import com.mapper.LockDao;
import com.mapper.ProvinceMapper;
import com.mapper.UserAnnotationMapper;
import com.mapper.UserMapper;
import com.utils.MyBatisUtil;

/***
 * mybatis如果想要打印sql语句的话，可以使用log4j.因为mybatis默认整合log4j
 * 
 * @author Administrator
 *
 */
public class TestMybatis {

	public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
		//test22();
		test19();
	}
	
	/***
	 * 动态sql  if
	 */
	public static void test22() {
		SqlSession openSession = MyBatisUtil.getSqlSession(true);
		UserMapper mapper = openSession.getMapper(UserMapper.class);
		  List<User> list= mapper.testIf("8");
		  //  List<User> list= mapper.testIf("");
		  //比较这两个发出的sql语句
		  System.out.println(list);
       
	}
	
	/***
	 *测试resultmap的用法
	 */
	public static void test21()  {
			SqlSession openSession = MyBatisUtil.getSqlSession(true);
			ProvinceMapper pm = openSession.getMapper(ProvinceMapper.class);
		    List<Province> list = pm.getProvince(1);
		    System.out.println(list);
	}

	/****
	 * 二级缓存测试  得使用一个sessionFactory来创建多个sqlsession
	 * 主配置文件
	 * <settings>
		<setting name="cacheEnabled" value="true" />
	</settings>
	映射文件
	<cache eviction="LRU" flushInterval="20000" size="2014" readOnly="true"/>
	 * @throws IOException 
	 */
	public static void test20()  {
		try {
			System.out.println("start..................");
			  SqlSessionFactory sqlSessionFactory = MyBatisUtil.getSqlSessionFactory();
			  SqlSession openSession  = sqlSessionFactory.openSession();
			UserMapper userMapper = openSession.getMapper(UserMapper.class);
			User user =userMapper.getUser(40);
			System.out.println(user.getName());
			openSession.close();
			
			SqlSession openSession1 = sqlSessionFactory.openSession();
			UserMapper userMapper1 = openSession1.getMapper(UserMapper.class);
			User user1 =userMapper1.getUser(40); 
			System.out.println(user1.getName());
			openSession1.close();
			System.out.println("end..................");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/***
	 * 把list集合存成字符串
	 */
	public static void test18() {
		SqlSession sqlSession = null;
		try {
			sqlSession = MyBatisUtil.getSqlSession();
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			Person p = new Person();
			List<String> list = new ArrayList<String>();
			list.add("足球");
			list.add("排球");
			list.add("音乐");
			list.add("读书");
			p.setInterest(list);
			userMapper.insertPerson(p);
			sqlSession.commit();
		} catch (Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}

	}
      
	/*****
	 * 测试缓存   测试发出几次sql 如果什么都不配置，默认是查询一次
	 */
	public static void test19() {
		SqlSession sqlSession = null;
		try {
			sqlSession = MyBatisUtil.getSqlSession();
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			List<Person> people = userMapper.getPerson(1);// 测试发出几次sql 如果什么都不配置，默认是查询一次
			List<Person> people2 = userMapper.getPerson(1);
			System.out.println(people);
			System.out.println(people2);
			for (Person person : people) {
				System.out.println(person);
			}
			sqlSession.commit();
		} catch (Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}

	}

	/***
	 * 数据库日期转换成实体类
	 */
	public static void test17() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	 	User user2 = userMapper.getUser2(42);
	 	System.out.println(user2.getName() + "," + user2.getRegTime());

	}

	/****
	 * 日期转换成时间戳字符串
	 */
	public static void test16() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setName("0814");
		user.setAge(12);
		user.setRegTime(new Date());
		int i = userMapper.insertUser(user);
		sqlSession.commit();

	}

	/****
	 * 得到主键
	 */
	public static void test15() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setName("0814");
		user.setAge(12);
		int i = userMapper.addUserAndGetID(user);
		System.out.println("主键ID：" + user.getId());// 提交前查询 测试注释掉 useGeneratedKeys="true" keyProperty="id" 比较两次查询结果
		sqlSession.commit();

	}

	/****
	 * 测试一对多
	 */
	public static void test14() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		LockDao mapper = sqlSession.getMapper(LockDao.class);
		Lock lock = mapper.getLock(6);
		System.out.println(lock);
		//System.out.println(lock.getKeys());
	}

	/***
	 * 得到所有的别名 包括程序内定的
	 */
	public static void test13() {
		SqlSessionFactory sqlSessionFactory = MyBatisUtil.getSqlSessionFactory();
		Configuration con = sqlSessionFactory.getConfiguration();
		Map<String, Class<?>> typeMap = con.getTypeAliasRegistry().getTypeAliases();
		for (Entry<String, Class<?>> entry : typeMap.entrySet()) {
			System.out.println(entry.getKey() + " ================> " + entry.getValue().getSimpleName());
		}
	}

	/****
	 * 注解 查询多条
	 * 
	 * @throws IOException
	 */
	public static void test12() throws IOException {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		UserAnnotationMapper mapper = sqlSession.getMapper(UserAnnotationMapper.class);
		// 执行查询操作，将查询结果自动封装成User返回
		List<User> lstUsers = mapper.getAll();
		// 使用SqlSession执行完SQL之后需要关闭SqlSession
		sqlSession.close();
		System.out.println(lstUsers);
	}

	/***
	 * 注解 查询一条
	 * 
	 * @throws IOException
	 */
	public static void test11() throws IOException {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		UserAnnotationMapper mapper = sqlSession.getMapper(UserAnnotationMapper.class);
		// 执行查询操作，将查询结果自动封装成User返回
		User user = mapper.getById(31);
		// 关闭SqlSession
		sqlSession.close();
		System.out.println(user.getName());
	}

	/***
	 * 注解 删除
	 * 
	 * @throws IOException
	 */
	public static void test10() throws IOException {
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);// 得到session，并且自动提交
		UserAnnotationMapper mapper = sqlSession.getMapper(UserAnnotationMapper.class);
		mapper.deleteById(34);
		sqlSession.close();
	}

	/****
	 * 注解 修改
	 * 
	 * @throws IOException
	 */
	public static void test9() throws IOException {
		SqlSession sqlSession = getSession();
		UserAnnotationMapper mapper = sqlSession.getMapper(UserAnnotationMapper.class);
		User user = new User();
		user.setId(36);
		user.setName("吃鸡");
		user.setAge(26);
		// 执行修改操作
		int retResult = mapper.update(user);
		sqlSession.commit();
		sqlSession.close();
	}

	/****
	 * 使用注解来实现增加 使用注解来实现增删改查 跟xml配置基本类似 但是读取是xml读取的是文件 注解读取的是类 <!-- 如果使用注解读取的是类 -->
	 * <mapper class="com.mapper.UserAnnotationMapper" /> <!-- 如果使用的是xml 读取的是配置文件
	 * --> <mapper resource="mapper/userMapper.xml" />
	 * 
	 * @throws IOException
	 */
	public static void test8() throws IOException {
		SqlSession sqlSession = getSession();
		// 得到UserAnnotationMapper接口的实现类对象，
		// UserAnnotationMapper接口的实现类对象由sqlSession.getMapper(UserAnnotationMapper.class)动态构建出来
		UserAnnotationMapper mapper = sqlSession.getMapper(UserAnnotationMapper.class);
		User user = new User();
		user.setName("用户xdp");
		user.setAge(20);
		int add = mapper.add(user);
		sqlSession.commit();
		// 使用SqlSession执行完SQL之后需要关闭SqlSession
		sqlSession.close();
		System.out.println(add);
	}

	/***
	 * 得到session
	 * @throws IOException
	 */
	public static SqlSession getSession() throws IOException {
		String resource = "mybatis.xml";
		// 读取配置文件
		Reader reader = Resources.getResourceAsReader(resource);
		// 获取会话工厂
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		SqlSession openSession = sqlSessionFactory.openSession();
		return openSession;
	}

	/****
	 * 最简单的示例 查询
	 */
	public static void test1() throws IOException {
		SqlSession openSession = getSession();
		// 查询
		String sql = "com.mapper.UserMapper.getUser";
		// 调用api查询
		User user = openSession.selectOne(sql, 24);
		System.out.println(user.getName());
	}

	/****
	 * 添加一条数据
	 * 
	 * @throws IOException
	 */
	public static void test2() throws IOException {
		SqlSession openSession = getSession();
		// 查询
		String sql = "com.mapper.UserMapper.addUser";
		// 调用api查询
		User userPa = new User();
		userPa.setAge(19);
		userPa.setName("张三222");
		int reuslt = openSession.insert(sql, userPa);
		openSession.commit();// 需要执行提交方法 否则保存不到数据库中
		openSession.close();
		System.out.println(reuslt);
	}

	/****
	 * 修改
	 * 
	 * @throws IOException
	 */
	public static void test3() throws IOException {
		SqlSession openSession = getSession();
		// 查询
		String sql = "com.mapper.UserMapper.userUpdate";
		// 调用api查询
		User userPa = new User();
		userPa.setId(35);
		userPa.setAge(44);
		userPa.setName("4444");
		int reuslt = openSession.insert(sql, userPa);
		openSession.commit();// 需要执行提交方法 否则保存不到数据库中
		openSession.close();
		System.out.println(reuslt);
	}

	/****
	 * 删除
	 * 
	 * @throws IOException
	 */
	public static void test4() throws IOException {
		SqlSession openSession = getSession();
		// 查询
		String sql = "com.mapper.UserMapper.delUser";
		// 调用api查询
		int delete = openSession.delete(sql, 35);
		openSession.commit();
		openSession.close();
		System.out.println(delete);
	}

	/****
	 * sql注入问题
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void test5() throws ClassNotFoundException, SQLException {
		//String username = "dzz1";// 这个可以查询出一条数据
		String password = "12345";
		String username = "username='  OR 1=1 -- ";// 这个可以查询出多条数据
		String sql = "SELECT id,username FROM user_table WHERE " + "username='" + username + "'AND " + "password='" + password + "'";
		// 上边的语句是sql注入的问题
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection con = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/cboot2?useSSL=false&serverTimezone=UTC", "root", "root");
		PreparedStatement stat = con.prepareStatement(sql);
		System.out.println(stat.toString());
		ResultSet rs = stat.executeQuery();
		while (rs.next()) {
			String id = rs.getString(1);
			String name = rs.getString(2);
			System.out.println("id:" + id + "---name:" + name);
		}

	}

	/*****
	 * 使用占位符来解决sql注入问题
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void test6() throws ClassNotFoundException, SQLException {
		String username = "username=' OR 1=1 -- ";
		// String username = "dzz1";
		String password = "12345";
		String sql = "SELECT id,username FROM user_table WHERE username=? AND password=?";
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection con = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/cboot2?useSSL=false&serverTimezone=UTC", "root", "root");
		PreparedStatement stat = con.prepareStatement(sql);
		stat.setString(1, username);
		stat.setString(2, password);
		System.out.println(stat.toString());
		ResultSet rs = stat.executeQuery();
		while (rs.next()) {
			String id = rs.getString(1);
			String name = rs.getString(2);
			System.out.println("id:" + id + "---name:" + name);
		}
	}

	/****
	 * 使用mybatis测试sql注入问题
	 * 
	 * @throws IOException
	 */
	public static void test7() throws IOException {
		SqlSession openSession = getSession();
		// 查询
		String sql = "com.mapper.UserTableMapper.login";
		// 调用api查询
		UserTable userTable = new UserTable();
		userTable.setUserName("''  OR 1=1 -- ");
		// userTable.setUserName("dzz1");
		userTable.setPassWord("12345");
		java.util.List<UserTable> listUserTable = openSession.selectList(sql, userTable);
		for (UserTable ub : listUserTable) {
			System.out.println(ub.getUserName());
		}
	}

}
